package com.rdc.example.dao.impl;

import com.rdc.example.bean.dto.ArticleDTO;
import com.rdc.example.dao.ArticleDao;

import com.rdc.example.entity.Article;


import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

/**
 * @author duck
 */
/*    private int id;
    private Inget userId;
    private Inget columId;
    private Boolean isTop;
    private String title;
    private String content;
    private LocalDateTime createdAt;
    private LocalDateTime updateAt;
* */

public class ArticleDaoImpl implements ArticleDao {

    public static void main(String[] args) throws Exception {
        ArticleDaoImpl dao = new ArticleDaoImpl();
        Article article = new Article();

        List<ArticleDTO> articleDTOList;
        List<Integer> ids= Arrays.asList(4,5,6,7);
        List<Article> articleList=dao.getArticleListByIds(ids);
        System.out.println(articleList);
    }

    /**
     * 插入文章
     * @param article
     * @return 插入结果
     * @throws Exception
     */
    @Override
    public boolean insert(Article article) throws Exception {
        // 进行数据库插入操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql ="INSERT INTO article (user_id ,title,content,column_id,is_top,likes) VALUES (?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            insertArticle(pstmt, article);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     * 删除文章
     * @return 删除结果
     */
    @Override
    public boolean deleteById(Integer id) throws Exception {
        // 进行数据库删除操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql = "delete from article where id=?" ;
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (SQLIntegrityConstraintViolationException e) {
            e.printStackTrace();
        }finally {
            closeResources(null,pstmt,conn);
        }
        return false;
    }

    /**
     * 更新文章
     * @param article
     * &#064;return更新结果
     */
    @Override
    public boolean update(Article article) throws Exception {
        // 进行数据库更新操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql = "update article set user_id =?,title=?,content=?,column_id=?,is_top=?,likes=?,updated_at=? where id=?";
            pstmt = conn.prepareStatement(sql);
            insertArticle(pstmt,article);
            pstmt.setObject (7, LocalDateTime.now() );
            pstmt.setInt(8,article.getId());
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     * 评论更新时间2
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public boolean updateTime(Integer id) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update article set updated_at = ? where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, LocalDateTime.now());
            pstmt.setInt(2, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     *
     * @param id
     * @param columnId
     * @return
     * @throws Exception
     */
    @Override
    public boolean addColumn(Integer id, Integer columnId) throws Exception {
        // 进行数据库更新操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql = "update article set column_id=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt (1, columnId);
            pstmt.setInt (2, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     *
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public boolean removeColumn(Integer id) throws Exception {
        // 进行数据库更新操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql = "update article set column_id=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt (1, Types.INTEGER);
            pstmt.setInt (2, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }


    /**
     *
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public boolean setTop(Integer id) throws Exception {
        // 进行数据库更新操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql = "update article set is_top=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setBoolean(1, true);
            pstmt.setInt (2, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     *
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public boolean cancelTop(Integer id) throws Exception {
        // 进行数据库更新操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql = "update article set is_top=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setBoolean(1, false);
            pstmt.setInt (2, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     * 点赞让article.like++
     * @param id
     * @return boolean
     * @throws Exception
     */
    @Override
    public boolean addLike(Integer id) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update article set likes = likes + 1 where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     * 取消点赞让article.like--
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public boolean reduceLike(Integer id) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            //保证不会减少到负数
            String sql = "update article set likes = greatest(likes - 1, 0) where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(null, pstmt, conn);
        }
    }


    /**
     * 通过id查找文章
     * @param id
     * @return article类
     * @throws Exception
     */
    @Override
    public Article getArticleById(Integer id) throws Exception {
        // 进行数据库查询操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Article article = null;
        try {
            conn = getConnection();
            String sql = "select * from article where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                article = getArticle(rs);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return article;
    }

    /**
     * 通过userId查找文章list
     * @param userId
     * @return articleList
     * @throws Exception
     */
    @Override
    public List<Article> listArticleByUserId(Integer userId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Article> articleList = new ArrayList<>();
        try {
            String sql = "SELECT * from article where user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Article article=getArticle(rs);
                articleList.add(article);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return articleList;
    }

    /**
     * 通过columnId找文章
     * @param columnId
     * @return
     * @throws Exception
     */
    @Override
    public List<Article> listArticleByColumnId(Integer columnId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Article> articleList = new ArrayList<>();
        try {
            String sql = "SELECT * from article where column_id=? order by is_top desc";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Article article=getArticle(rs);
                articleList.add(article);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return articleList;
    }


    /**
     *点赞降序获取文章list
     * @return userList
     * @throws Exception
     */
    @Override
    public List<Article> listArticleSortByLikesDesc() throws Exception{
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Article> articleList = new ArrayList<>();
        try {
            String sql = "select * from article order by likes desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Article article = getArticle(rs);
                articleList.add(article);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return articleList;
    }

    /**
     *评论时间降序获取文章list
     * @return userList
     * @throws Exception
     */
    @Override
    public List<Article> listArticleSortByUpdatedAtDesc() throws Exception{
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Article> articleList = new ArrayList<>();
        try {
            String sql = "select * from article order by updated_at desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Article  article = getArticle(rs);
                articleList.add(article);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return articleList;
    }

    /**
     * 查找所有文章
     * @return articleList
     * @throws Exception
     */
    @Override
    public List<Article> listArticle() throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Article> articleList = new ArrayList<>();
        try {
            String sql = "select * from article";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Article article = getArticle(rs);
                articleList.add(article);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return articleList;
    }

    /**
     *
     * @param articleIds
     * @return
     */
    @Override
    public List<Article> getArticleListByIds(List<Integer> articleIds) {
        List<Article> articleList = new ArrayList<>();
        if (articleIds == null || articleIds.isEmpty()) {
            return articleList;
        }
        String sql = "SELECT * FROM article WHERE id IN (" +
                articleIds.stream()
                        .filter(Objects::nonNull)
                        .map(String::valueOf)
                        .collect(Collectors.joining(",")) + ")";
        try (Connection conn =getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Article article = getArticle(rs);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return articleList;
    }

    /**
     *
     * @param keyword
     * @return
     * @throws Exception
     */
    @Override
    public List<Article> searchArticlesByTitle(String keyword) throws Exception {
        List<Article> articleList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "select * from `article` where title like ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                articleList.add(getArticle(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return articleList;
    }

    private Article getArticle(ResultSet rs) throws SQLException {
        Article article = new Article();
        article.setId(rs.getInt("id"));
        article.setTitle(rs.getString("title"));
        article.setContent(rs.getString("content"));
        article.setIsTop(rs.getBoolean("is_top"));
        article.setColumId(rs.getInt("column_id"));
        article.setUserId(rs.getInt("user_id"));
        article.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        article.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
        article.setLikes(rs.getInt("likes"));
        return article;
    }
//    private ArticleDTO getArticleDTO(ResultSet rs) throws SQLException {
//        ArticleDTO articleDTO = new ArticleDTO();
//        articleDTO.setId(rs.getInt("id"));
//        articleDTO.setTitle(rs.getString("title"));
//        articleDTO.setContent(rs.getString("content"));
//        articleDTO.setIsTop(rs.getBoolean("is_top"));
//        articleDTO.setColumId(rs.getInt("column_id"));
//        articleDTO.setUserId(rs.getInt("user_id"));
//        articleDTO.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
//        articleDTO.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
//        articleDTO.setLikes(rs.getInt("likes"));
//        return articleDTO;
//    }
    private void insertArticle(PreparedStatement pstmt, Article article) throws SQLException {
        pstmt.setInt(1, article.getUserId());
        pstmt.setString(2, article.getTitle());
        pstmt.setString(3, article.getContent());
        if(article.getColumId()==null){
            pstmt.setNull(4, Types.INTEGER);
        }else{
            pstmt.setInt(4,article.getColumId());
        }
        if(article.getIsTop()==null){
            pstmt.setBoolean(5, false);
        }else {
            pstmt.setBoolean(5, article.getIsTop());
        }
        if(article.getLikes()==null){
            pstmt.setInt(6, 0);
        }else {
            pstmt.setInt(6, article.getLikes());
        }
    }
}
